MySQL EXPLAIN 简单了解

八月 13, 2021 阅读量

MySQL

帮朋友解决了一个查询问题(查询报错The table ‘C:\Windows\TEMP#sqlxxxxxx’ is full)。试过增加tmp_table_size和max_heap_table_size的大小，然并卵；用SQL_BIG_RESULT强制跑，结果把C盘100多G的空间干满了，No space left on device；最后用了EXPLAIN分析SQL语句的执行计划信息，来去除临时表，然后问题解决。了解到了以前不了解的知识，比如最左(前缀)匹配原则。为了以后不在踩坑，这里简单写一以下EXPLAIN的信息以备后用。

EXPLAIN简介

EXPLAIN可以获取SQL语句的执行计划信息，可用于SELECT，DELETE，INSERT，REPLACE，和UPDATE语句，用来分析优化SQL以及表结构。执行（EXPLAIN + SQL语句）效果如下：

mysql> explain select 1 from dual;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN列信息

id
SELECT的标识符，按照SELECT出现顺序增长，UNION查询id为NULL。
select_type
SELECT的类型

值	意义
SIMPLE	简单查询（不使用UNION和子查询）
PRIMARY	复杂查询最外层的SELECT
UNION	UNION中第二个或以后的SELECT
DEPENDENT UNION	UNION中第二个或以后的SELECT（依赖于外部查询）
UNION RESULT	UNION的结果
SUBQUERY	子查询第一个SELECT
DEPENDENT SUBQUERY	子查询第一个SELECT（依赖于外部查询）
DERIVED	派生表
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	子查询无法被物化，每次都需要重新评估
UNCACHEABLE UNION	UNION中第二个或以后的无法被物化的SELECT（类似UNCACHEABLE SUBQUERY）

table
输出行所引用表名，或者以下值：

值	意义
<unionM,N>	id为M和N行的并集
<derivedN>	id为N行的派生表结果
<subqueryN>	id为N行的物化子查询结果

partitions
匹配记录所在的分区（非分区表该值为NULL）
type
连接类型，效率从高到低排列为：

值	意义
system	表只有一行（类似系统表，const连接类型的一个特例）
const	该表最多有一个匹配行，该列的值可以被优化器的其余部分视为常量
eq_ref	从该表中读取一行与前一张表的所有行组合（使用主键索引或唯一非空索引）
ref	从该表中读取具有匹配索引值的所有行与前一张表的所有行的组合（不使用唯一索引，使用普通索引或唯一性索引的部分前缀）
fulltext	使用全文索引
ref_or_null	类似ref，但是需要额外搜索NULL
index_merge	使用索引合并的优化方法
unique_subquery	替代了 value IN (SELECT primary_key FROM single_table WHERE some_expr) 形式的eq_ref一些IN子查询
index_subquery	类似unique_subquery，替代了IN子查询，适用于 value IN (SELECT key_column FROM single_table WHERE some_expr) 形式的子查询中的非唯一索引
range	使用索引来检索给定范围的行
index	与ALL一样，但是扫描索引树（全索引扫描）
ALL	全表扫描

possible_keys
该表中可以使用哪个索引去查找行
key
该表中实际使用索引去查找行
key_len
该表中实际使用索引长度（可以推断使用组合索引中的多少）
ref
key列记录的索引中，表查找值所用到的列，常量（const）或函数（func）
rows
读取并检测的行数
filtered
按表条件过滤的行的估计百分比，最大值为100（没有发生行过滤）
Extra
附加信息，比较常见重要的如下

值	意义
Using index	使用索引树中的信息从表中检索列信息
Using where	使用WHERE限制返回的行
Using temporary	需要创建一个临时表来保存结果（比如GROUP BY和ORDER BY会出现，需要优化，一般用添加索引来优化）
Using filesort	对结果使用一个外部索引排序，而不是按索引次序从表里读取行（需要优化，一般用索引来优化）

查看评论